IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SEARCH]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SEARCH]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE  PROCEDURE [dbo].[SEARCH]

	(
		@SOURCETABLE NVARCHAR(200), --THE SOURCE TABLE CAN BE A REAL TABLE OR A VIEW
		@PRIMARYKEY NVARCHAR(255) = 'ID', 
		@WHERECLAUSE NVARCHAR(2000) = '',
		@ORDERBYCLAUSE NVARCHAR(2000) = '', 
		@PAGEINDEX INT = 1, 
		@PAGESIZE INT = 5, 		
		@TOTALCOUNT INT OUTPUT
	)

AS
SET NOCOUNT ON
	
	DECLARE 
		@SQL_SUFFIX	NVARCHAR(4000)
	
	DECLARE 
        @KEYORDER NVARCHAR(50)
    
	SET @KEYORDER = ' ORDER BY ' +@PRIMARYKEY + ' ASC '

	IF @ORDERBYCLAUSE = ''
		SET @ORDERBYCLAUSE = @PRIMARYKEY + ' ASC'
		
	IF @WHERECLAUSE <> ''
		SET @SQL_SUFFIX = ' WHERE ' + @WHERECLAUSE + ' ORDER BY ' + @ORDERBYCLAUSE
	ELSE
		SET @SQL_SUFFIX = ' ORDER BY ' + @ORDERBYCLAUSE
			
	IF @WHERECLAUSE = '' 
		SET @WHERECLAUSE=' 1=1 '		
	--CREATE A TEMPORARY TABLE FOR PAGING	
	CREATE TABLE #IDTABLE	
	(
		TMPNUMBER INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
		ORGNUMBER NVARCHAR(40) NOT NULL,
		TOTALCOUNT INT
	)
	--PRINT('INSERT INTO #IDTABLE ([ORGNUMBER]) ' + 'SELECT [' + @PRIMARYKEY + '] FROM [' + @SOURCETABLE + ']' + ' WHERE ' + @WHERECLAUSE + @KEYORDER)
	--EXEC('INSERT INTO #IDTABLE ([ORGNUMBER]) ' + 'SELECT [' + @PRIMARYKEY + '] FROM [' + @SOURCETABLE + ']' + ' WHERE 1=1 AND ' + @WHERECLAUSE + @KEYORDER)	
	PRINT('INSERT INTO #IDTABLE ([ORGNUMBER]) ' + 'SELECT [' + @PRIMARYKEY + '] FROM [' + @SOURCETABLE + ']' + @SQL_SUFFIX)
	EXEC('INSERT INTO #IDTABLE ([ORGNUMBER]) ' + 'SELECT [' + @PRIMARYKEY + '] FROM [' + @SOURCETABLE + ']' + @SQL_SUFFIX)			
	SET @TOTALCOUNT = @@ROWCOUNT
	UPDATE #IDTABLE SET TOTALCOUNT =  @TOTALCOUNT	
--	IF(@PAGEINDEX = 1)
--		BEGIN
--			SET ROWCOUNT @PAGESIZE
--			
--			PRINT('SELECT * FROM [' + @SOURCETABLE + ']' + @SQL_SUFFIX)
--			EXEC('SELECT * FROM [' + @SOURCETABLE + ']' + @SQL_SUFFIX)
--			
--			SET ROWCOUNT 0
--		END
--	ELSE
		BEGIN						
			--DELETE ALL UNUSED ROW NUMBERS
			DELETE FROM #IDTABLE
			WHERE [TMPNUMBER] <= @PAGESIZE*(@PAGEINDEX-1) OR [TMPNUMBER] > @PAGESIZE * @PAGEINDEX			
			PRINT('SELECT * FROM #IDTABLE INNER JOIN [' + @SOURCETABLE + '] ON #IDTABLE.[ORGNUMBER] = [' + @SOURCETABLE + '].[' + @PRIMARYKEY + '] ORDER BY [TMPNUMBER]')
			PRINT('SELECT * FROM #IDTABLE INNER JOIN [' + @SOURCETABLE + '] ON #IDTABLE.[ORGNUMBER]  = [' + @SOURCETABLE + '].[' + @PRIMARYKEY + '] ')			
			EXEC('SELECT * FROM #IDTABLE INNER JOIN [' + @SOURCETABLE + '] ON #IDTABLE.[ORGNUMBER]  = [' + @SOURCETABLE + '].[' + @PRIMARYKEY + '] ' + @SQL_SUFFIX)			
		END
	
	RETURN


GO


